package wujinye.com.dao;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import wujinye.com.constant.Const;
import wujinye.com.dao.base.BaseDao;
import wujinye.com.model.Article;
import wujinye.com.utils.JDBCUtil;
import wujinye.com.utils.StrUtil;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class ArticleDao extends BaseDao<Article> {
    /**
     * 查找文章列表，用作页面展示
     *
     * @param search
     * @param page
     * @return
     */
    public List<Map<String, Object>> findArticleList(String search, int page) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        StringBuilder sql = new StringBuilder();
        List<Map<String, Object>> list = null;
        sql.append("select a.id,a.title,a.content,a.browse_count as views,a.publish_date as publishDate,");
        sql.append("a.publish_username as authorName,a.user_id as userId,f.total as total ");
        sql.append("from article a left join ");
        sql.append("(select count(*) as total,a_id from favorite group by a_id) f ");
        sql.append("on a.id = f.a_id");
        try {
            if (StrUtil.checkString(search)) {
                sql.append(" where a.title like ? ");
                sql.append("order by a.id limit ?,?");
                search = search + "%";
                list = qr.query(sql.toString(), new MapListHandler(), search, (page - 1) * Const.MAX_ARTICLE_PAGE_SIZE, Const.MAX_ARTICLE_PAGE_SIZE);
            } else {
                sql.append(" order by a.id limit ?,?;");
                list = qr.query(sql.toString(), new MapListHandler(), (page - 1) * Const.MAX_ARTICLE_PAGE_SIZE, Const.MAX_ARTICLE_PAGE_SIZE);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 统计文章总数
     *
     * @param search
     * @return
     */
    public long getTotalArticle(String search) {
        long total = 0L;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            String sql = "select count(*) as total from article a";
            if (StrUtil.checkString(search)) {
                sql = sql + " where a.title like ? ;";
                search = search + "%";
                total = qr.query(sql.toString(), new ScalarHandler<Long>(), search);
            } else {
                total = qr.query(sql.toString(), new ScalarHandler<Long>());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }

    /**
     * 获取文章的浏览次数
     *
     * @param id
     * @return
     */
    public Map<String, Object> getViews(Integer id) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        Map<String, Object> map = null;
        try {
            String sql = "select browse_count as views from article where id = ?;";
            map = qr.query(sql, new MapHandler(), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return map;
    }

    /**
     * 文章浏览次数+1
     *
     * @param id
     * @param views
     */
    public void addViews(Integer id, long views) {
        String sql = "update article set browse_count = ? where id = ?;";
        Object[] obj = {views, id};
        adu(sql, obj);
    }

    /**
     * 根据文章id查找对应文章的信息
     *
     * @param id
     * @return
     */
    public Map<String, Object> findArticle(Integer id) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        Map<String, Object> map = null;
        try {
            String sql = "select a.id,a.title,a.content,a.browse_count as views,a.publish_date as publishDate,\n" +
                    "a.publish_username as authorName,a.user_id as userId,f.total as total \n" +
                    "from article a left join \n" +
                    "(select count(*) as total,a_id from favorite group by a_id) f \n" +
                    "on a.id = f.a_id \n" +
                    "where a.id = ?";
            map = qr.query(sql, new MapHandler(), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return map;
    }

    /**
     * 添加文章
     *
     * @param article
     */
    public void addArticle(Article article) {
        String sql = "insert into article values(?,?,?,?,?,?,?);";
        Object[] obj = {article.getId(), article.getTitle(), article.getContent(), article.getBrowseCount(), article.getPublishDate(), article.getPublishUsername(), article.getUserId()};
        adu(sql, obj);
    }

    /**
     * 查找用户收藏的文章信息
     *
     * @param search
     * @param page
     * @param uId
     * @return
     */
    public List<Map<String, Object>> findMyArticleList(String search, int page, Integer uId) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        StringBuilder sql = new StringBuilder();
        List<Map<String, Object>> list = null;
        sql.append("select a.id,a.title,a.content,a.browse_count as views,a.publish_date as publishDate,");
        sql.append("a.publish_username as authorName,a.user_id as userId,t.total as total ");
        sql.append("from favorite f left join article a on f.a_id = a.id ");
        sql.append("left join (select count(*) as total,a_id from favorite group by a_id) t on a.id = t.a_id ");
        sql.append("where f.u_id = ? ");
        try {
            if (StrUtil.checkString(search)) {
                sql.append("and a.title like ? ");
                sql.append("order by a.id limit ?,?;");
                search = search + "%";
                list = qr.query(sql.toString(), new MapListHandler(), uId, search, (page - 1) * Const.MAX_ARTICLE_PAGE_SIZE, Const.MAX_ARTICLE_PAGE_SIZE);
            } else {
                sql.append("order by a.id limit ?,?;");
                list = qr.query(sql.toString(), new MapListHandler(), uId, (page - 1) * Const.MAX_ARTICLE_PAGE_SIZE, Const.MAX_ARTICLE_PAGE_SIZE);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 统计用户收藏的文章总数
     * @param search
     * @param uId
     * @return
     */
    public long getTotalMyArticle(String search, Integer uId) {
        long total = 0L;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("select count(*) as total from favorite f left join article a on f.a_id = a.id ");
            sql.append("where f.u_id = ?");
            if (StrUtil.checkString(search)) {
                sql.append(" and a.title like ?;");
                search = search + "%";
                total = qr.query(sql.toString(), new ScalarHandler<Long>(), uId,search);
            } else {
                total = qr.query(sql.toString(), new ScalarHandler<Long>(),uId);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }
}
